Amazon AthenaでELBログをSQLで解析する #reinvent

西澤です。目新しさの無いエントリーですが、溜め込んだELBログを解析する必要があったので試してみることにしました。Amazon Athenaは東京リージョンでは使えないので、OregonリージョンのAthenaから、東京リージョンのS3バケットをデータソースとして検証しました。

と言ってもほぼ下記ページにやり方は詳しく載っていますので、公式ページを見ていただく形でも構いません。

• Analyzing Data in S3 using Amazon Athena | AWS Big Data Blog

Athenaでデータベースを作成する

データベースエンジニアで無い担当者からすると、専用のSQLクライアントを用意すること無く、AWS Management Console上で全ての操作が完結するというのは大変嬉しいです。GUIで操作することもできますが、テーブルを作成するデータベースを先に別に用意したかったので、Query Editorから作業してみました。

CREATE DATABASE elblogdb;

簡単ですね。

Athenaでテーブルを作成する

こちらもSQL EditorからCREATE TABLEで作成します。今回は公式ページの情報からSQLをそのまま拝借することができましたが、独自のテーブル定義が必要となる場合にはHive DDLのお作法を少し勉強しないといけないようです。ROW FORMATに指定できるパラメータ等を整理しておきたいところですが、今回はさらりとそのまま実行してしまいます。変更した箇所は作成するテーブル名の指定と、LOCATIONに指定しているELBログのS3パスだけです。今回は2016年11月分のログを対象にしてみました。

CREATE EXTERNAL TABLE IF NOT EXISTS elblogdb.tablename_aaa_201611 (
request_timestamp string,
elb_name string,
request_ip string,
request_port int,
backend_ip string,
backend_port int,
request_processing_time double,
backend_processing_time double,
client_response_time double,
elb_response_code string,
backend_response_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
url string,
protocol string,
user_agent string,
ssl_cipher string,
ssl_protocol string )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'

WITH SERDEPROPERTIES (
'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' )

LOCATION 's3://log_s3_bucket_aaa/logs/elb/elbname_aaa/AWSLogs/123456789012/elasticloadbalancing/ap-northeast-1/2016/11/';

AthenaでSQLを実行する

それでは作成したテーブルに対して任意のクエリを試してみます。何度か試しましたが、概ね10秒強で結果が返ってきました。

select * from elblogdb.tablename_aaa_201611
where elb_response_code <> '200'
order by request_timestamp;

ちなみに、今回スキャン対象にした2016年11月分のELBログですが、1400以上のオブジェクトが合計600MB超のサイズとなっていましたが、Athenaを使うことで自由にSQLで解析できる環境が10分足らずで出来上がってしまいました。これは本当に便利だなと感じました。

$ aws s3 ls s3://log_s3_bucket_aaa/logs/elb/elbname_aaa/AWSLogs/123456789012/elasticloadbalancing/ap-northeast-1/2016/11/ --recursive | wc -l
1441
$ aws s3 ls s3://log_s3_bucket_aaa/logs/elb/elbname_aaa/AWSLogs/123456789012/elasticloadbalancing/ap-northeast-1/2016/11/ --recursive | awk '{sum+=$3}END{print sum}'
636543241

スキャン対象のデータがより大きくなって来ると、性能や課金に影響してきますので、パーティショニングの検討も必要になると思います。

Amazon Athena利用時の注意事項

既知の制限、上限等は下記ページにまとまっていますので、ひと通り見ておくと良いと思います。

• Known Limitations — User Guide
• Service Limits — User Guide

特に今回少しだけハマったのが、こちらの既知の制限です。

In the LOCATION clause, use a trailing slash for your folder or bucket, NOT filenames or glob

LOCATIONに指定するS3パスの終わりは、バケット名、または、フォルダである必要がありました。

• OK
• s3://s3_bucket_name/
• s3://s3_bucket_name/folder_aaa/
• s3://s3_bucket_name/folder_aaa/prefix_folder_bbb/
• NG
• s3://s3_bucket_name/fold*
• s3://s3_bucket_name/prefix_
• s3://s3_bucket_name/data.txt

このNGのケースについて注意ですが、CREATE TABLE時にはエラーとはなりませんが、クエリを発行するとデータが0件になりました。ご注意ください。フォルダ配下は再帰的に処理されるので、上位のフォルダを指定する形で問題ないのですが、中途半端なprefix指定はできないと覚えておくと良いと思います。

まとめ

これまでもAWSのログ運用のベストプラクティスは、とりあえずS3に格納しておく、というのが鉄則でしたが、Athenaの登場でさらに便利に自由にS3内のデータ扱えるようになりました。S3にデータがありさえすれば、本当に簡単に利用することができますので、Amazon Athenaをまだ触ったことが無い方もぜひ試してみていただければと思います。

どこかの誰かのお役に立てば嬉しいです。